﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Data;

namespace DAL
{
    public class User { 
    
        public string UserId;
        public string typeId;
        public string pId;
    }
    public class ClientService
    {
        public static Client AddClient(Client client)
        {
            string sql = string.Format("insert into Client(PersonalInformationId) values({0});select @@IDENTITY;", client.PersonalInformationId);
            int id = DBHelper.GetScalar(sql);
            return GetClientById(id);
        }

        public static int GetPersonalInformationIdById(int id)
        {
            Client client = GetClientById(id);
            if (client == null)
                return -1;
            else
                return client.PersonalInformationId;
        }

        public static Client GetClientById(int id)
        {
            string sql = string.Format("select * from Client where Id={0}", id);
            return GetClientBySql(sql);
        }

        private static Client GetClientBySql(string sql)
        {
            List<Client> list = GetClientsBySql(sql);
            if (list.Count() > 0)
                return list[0];
            return null;
        }

        private static List<Client> GetClientsBySql(string sql)
        {
            List<Client> list = new List<Client>();
            DataTable dt = DBHelper.GetDataSet(sql);
            foreach (DataRow row in dt.Rows){
                Client client = new Client();
                client.Id = (int)row["Id"];
                client.PersonalInformationId = (int)row["PersonalInformationId"];
                list.Add(client);
            }
            return list;
        }
        public static string GetSingleValueFromSql(string strSql)//根据sql语句获得一个单值字符串
        {
            try
            {
                string result = DBHelper.GetScalar(strSql).ToString();
                return result;
            }
            catch
            {
                return "";
            }

        }
        ///检查是否存在用户
        ///参数 userName
        ///返回 true false
        public static bool isExistUser(string userName)
        {

            string strSql = "select * from client ,PersonalInformation where Client.PersonalInformationId=PersonalInformation.Id and Nickname = '" + userName + "'";
            string strSql2 = "select * from HouseHolder ,PersonalInformation where HouseHolder.PersonalInformationId=PersonalInformation.Id and Nickname='" + userName + "'";
            string count = GetSingleValueFromSql(strSql);
            string count2 = GetSingleValueFromSql(strSql2);
            if (count != "" && count != "0")
            {
                return true;
            }
            if (count2 != "" && count2 != "0")
            {
                return true;
            }
            return false;

        }
        ///检查密码是否正确
        ///参数 PassWord,
        ///返回 true false
        public static User CheckPass(string userName, string passWord)
        {
            string strSql = "select Password,c.Id,c.PersonalInformationId from client c,PersonalInformation p where c.PersonalInformationId=p.Id and Nickname ='" + userName + "'";
            string strSql2 = "select Password,h.Id,h.PersonalInformationId,h.IsChecked from HouseHolder h,PersonalInformation p where h.PersonalInformationId=p.Id and Nickname='" + userName + "'";
            DataTable result = DBHelper.GetDataSet(strSql);
            DataTable result2 = DBHelper.GetDataSet(strSql2);
            User u = new User();
            if (result.Rows.Count != 0)
            {
               
                if ((passWord).Equals(Convert.ToString(result.Rows[0][0])))
                {
                    u.UserId =Convert.ToString(result.Rows[0][1]);
                    u.pId = Convert.ToString(result.Rows[0][2]);
                    u.typeId = "1";
                    return u;
                }
                return null;
            }
            if (result2.Rows.Count != 0)
            {
                string str = result2.Rows[0][0].ToString();
                if ((passWord).Equals(Convert.ToString(result2.Rows[0][0])))
                {
                    u.UserId = Convert.ToString(result2.Rows[0][1]);
                    u.pId = Convert.ToString(result2.Rows[0][2]);
                    u.typeId = "0";
                    return u;
                }
                return null;
            }

            return null;
        }

        public static int Count()
        {
            string sql = string.Format("select count(*) from Client");
            return DBHelper.GetScalar(sql);
        }

        
    }
}
